﻿using QuanLyGiaoVuDAL.ADOWrapper;
using QuanLyGiaoVuDAL.DTO;
using QuanLyGiaoVuDAL.Mapper;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;

namespace QuanLyGiaoVuDAL.Repositories.DatabaseRepositories
{
    public class BangDiemRepository:IBangDiemRepository
    {

        public BangDiemDTO GetBangDiemById(Guid maBangDiem)
        {
            SqlParameter[] parameters = new SqlParameter[1];
            parameters[0] = DBParameterFactory.Create("MaBangDiem", maBangDiem);
            var dbResult = StoreProcedureExecutor.ExecuteStoreProcedure("[dbo].[usp_BangDiemSelect]", parameters);
            try
            {
                var dataRow = dbResult.Tables[0].Rows[0];
                return dataRow.ToBangDiem2DTO();
            }
            catch (Exception ex)
            {
                return null;
            }
        }
        public IEnumerable<BangDiemDTO> GetAllBangDiem()
        {
            List<BangDiemDTO> listBangDiem = new List<BangDiemDTO>();
            SqlParameter[] parameters = new SqlParameter[1];
            parameters[0] = DBParameterFactory.Create("MaBangDiem", SqlDbType.UniqueIdentifier, DBNull.Value);
            var dbResult = StoreProcedureExecutor.ExecuteStoreProcedure("[dbo].[usp_BangDiemSelect]", parameters);
            try
            {
                var caTable = dbResult.Tables[0].Rows;
                foreach (DataRow dataRow in caTable)
                {
                    listBangDiem.Add(dataRow.ToBangDiem2DTO());
                }
                return listBangDiem.AsEnumerable();
            }
            catch (Exception ex)
            {
                return null;
            }
        }
        public IEnumerable<BangDiemDTO> GetBangDiemSinhVien(Guid SinhVienId)
        {
            List<BangDiemDTO> listBangDiem = new List<BangDiemDTO>();
            SqlParameter[] parameters = new SqlParameter[1];
            parameters[0] = DBParameterFactory.Create("SinhVienId", SinhVienId);
            var dbResult = StoreProcedureExecutor.ExecuteStoreProcedure("[dbo].[usp_BangDiem_SinhVien_Select]", parameters);
            try
            {
                var caTable = dbResult.Tables[0].Rows;
                foreach (DataRow dataRow in caTable)
                {
                    listBangDiem.Add(dataRow.ToBangDiemDTO());
                }
                return listBangDiem.AsEnumerable();
            }
            catch (Exception ex)
            {
                return null;
            }
        }
        public BangDiemDTO InsertBangDiem(BangDiemDTO bangDiemDTO)
        {
            var dbResult = StoreProcedureExecutor.ExecuteStoreProcedure("[dbo].[usp_BangDiemInsert]", GetParameterArray(bangDiemDTO));
            try
            {
                var dataRow = dbResult.Tables[0].Rows[0];
                return dataRow.ToBangDiem2DTO();
            }
            catch (Exception ex)
            {
                return null;
            }
        }
        public BangDiemDTO UpdateBangDiem(BangDiemDTO bangDiemDTO)
        {
            var dbResult = StoreProcedureExecutor.ExecuteStoreProcedure("[dbo].[usp_BangDiemUpdate]", GetParameterArray(bangDiemDTO));
            try
            {
                var dataRow = dbResult.Tables[0].Rows[0];
                return dataRow.ToBangDiem2DTO();
            }
            catch (Exception ex)
            {
                return null;
            }
        }
        public bool DeleteBangDiem(Guid maBangDiem)
        {
            try
            {
                var dbResult = StoreProcedureExecutor.ExecuteStoreProcedure("[dbo].[usp_BangDiemDelete]", DBParameterFactory.Create("@MaBangDiem", maBangDiem));
                return true;
            }
            catch (Exception ex)
            {
                return false;
            }
        }

        #region Private Methods
        private System.Data.SqlClient.SqlParameter[] GetParameterArray(BangDiemDTO banDiemDTO)
        {
            SqlParameter[] parameters = new SqlParameter[5];
            parameters[0] = DBParameterFactory.Create("MaBangDiem", banDiemDTO.MaBangDiem);
            parameters[1] = DBParameterFactory.Create("SinhVienId", banDiemDTO.SinhVienId);
            parameters[2] = DBParameterFactory.Create("MaMonHoc", banDiemDTO.MaMonHoc);
            parameters[3] = DBParameterFactory.Create("DiemHocPhan", banDiemDTO.DiemHocPhan);
            parameters[4] = DBParameterFactory.Create("MaHocKy", banDiemDTO.MaHocKy );
            return parameters;
        }
        #endregion


        public bool InsertOrUpdateBangDiem(BangDiemDTO bangDiemDTO)
        {
            try
            {
                var dbResult = StoreProcedureExecutor.ExecuteStoreProcedure("[dbo].[BANGDIEM_InsertUpdate]", GetParameterArray(bangDiemDTO));
                return true;
            }
            catch (Exception ex)
            {
                return false;
            }
        }
    }
}
